-- @owner: zhao-pengcheng-g
-- @date: 2022/01/24
-- @testpoint:约束触发器 违反规则，合理报错

--step1:建表;expect:成功
drop table if exists table_clob_0012;
create table table_clob_0012(c_id int not null,b_clob clob) with (storage_type=ustore);
--step2:创建函数;expect:成功
create or replace function tri_update_func_0012() returns trigger as
$$
declare
declare  cc_clob clob;
begin
 update table_clob_0012 set b_clob= b_clob || '111111111' where c_id=1;
return new;
end;
$$ language plpgsql;
/
--step3:创建约束触发器 违反规则,expect:合理报错
--合理报错
create constraint  trigger trigger_table_clob_0012 after insert on table_clob_0012
not deferrable  initially deferred
for each row
execute procedure tri_update_func_0012();
--成功
create constraint  trigger trigger_table_clob_0012 after insert on table_clob_0012
not deferrable  initially immediate
for each row
execute procedure tri_update_func_0012();
--合理报错
create trigger trigger_table_clob_0012 after insert on table_clob_0012
initially deferred
for each row
execute procedure tri_update_func_0012();
/
--合理报错
create trigger trigger_table_clob_0012 after insert on table_clob_0012
initially immediate
for each row
execute procedure tri_update_func_0012();
/
--合理报错
create trigger trigger_table_clob_0012 after insert on table_clob_0012
deferrable
for each row
execute procedure tri_update_func_0012();
/
--合理报错
create trigger trigger_table_clob_0012 after insert on table_clob_0012
NOT deferrable
for each row
execute procedure tri_update_func_0012();
/
--step4:环境清理;expect:成功
drop trigger trigger_table_clob_0012 on table_clob_0012 cascade;
drop table table_clob_0012 cascade;
drop function tri_update_func_0012;